package com.dsmbase.corp.project.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import java.util.List;


import com.dsmbase.corp.project.domain.ProjectFlowNode;

@Component
public class ProjectFlowNodeDao {

	private JdbcTemplate jdbcTemplate;
	
	private static final String SELECT_BY_ID_SQL = "select id, project_id, node_id, plan_workload, act_workload, start_time, end_time, status, user_id, proxy, weight from project_flow_node where id = ?";
	private static final String SELECT_ALL_SQL = "select * from project_flow_node where project_id = ? order by weight";
	private static final String SELECT_BY_USERID = "select * from project_flow_node where user_id = ?";
	private static final String INSERT_NEW_PROJECT = "insert project_flow_node values(null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	private static final String UPDATE_SQL = "update project_flow_node set project_id = ?, node_id = ?, plan_workload = ?, act_workload = ?, start_time = ?, end_time = ?, status = ?, user_id = ?, proxy = ?, weight = ?  where id = ?";
	
	@Autowired
	private DataSource dataSource;
	
	@Autowired
	public void setDataSource(DataSource ds) {
		dataSource = ds;
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	public ProjectFlowNode getProjectNodeById(int id) throws SQLException {
		Object[] params = new Object[] { id };
		ProjectFlowNode projectNode = null;
		try {
			projectNode = (ProjectFlowNode) jdbcTemplate.queryForObject(SELECT_BY_ID_SQL,
					params, new ProjectFlowNodeRowMapper());
		} catch (EmptyResultDataAccessException e) {
			throw new SQLException(e);
		}
		return projectNode;
	}
	
	public List<ProjectFlowNode> getAllProjectNodesByProjectId(int projectID) throws SQLException {
		Object[] params = new Object[] { projectID };
		List<ProjectFlowNode> list = null;
		list = jdbcTemplate.query(SELECT_ALL_SQL, params , new ProjectFlowNodeRowMapper());
		return list;
	}
	
	public List<ProjectFlowNode> getAllProjectNodesByUserId(int userId) throws SQLException {
		Object[] params = new Object[] { userId };
		List<ProjectFlowNode> list = null;
		list = jdbcTemplate.query(SELECT_BY_USERID, params , new ProjectFlowNodeRowMapper());
		return list;
	}
	
	public void createProjectNode(ProjectFlowNode node) throws SQLException {
		Object[] params = new Object[] { node.getProjectId(), node.getNodeId(), node.getPlanWorkLoad(), node.getActWorkLoad(), node.getStatus(), node.getStartTime(), node.getEndTime(), node.getUserId(), node.getProxy(), node.getWeight()};
		int[] argTypes = new int[] { Types.INTEGER, Types.INTEGER , Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.TIMESTAMP, Types.TIMESTAMP, Types.INTEGER, Types.INTEGER, Types.INTEGER };
		jdbcTemplate.update(INSERT_NEW_PROJECT, params, argTypes);
	}
	
	public class ProjectFlowNodeRowMapper implements RowMapper<ProjectFlowNode> {

		public ProjectFlowNode mapRow(ResultSet rs, int index) throws SQLException {
			ProjectFlowNode projectNode = new ProjectFlowNode(rs.getInt("id"), rs.getInt("project_id"),
					rs.getInt("node_id"), rs.getInt("plan_workload"), rs.getInt("act_workload"), 
					rs.getInt("status"), rs.getTimestamp("start_time"), rs.getTimestamp("end_time"), rs.getInt("user_id"), rs.getInt("proxy"), rs.getInt("weight"));
			return projectNode;
		}

	}

	public void updateProjectNode(ProjectFlowNode node) throws SQLException {
		Object[] params = new Object[] { node.getProjectId(), node.getNodeId(), node.getPlanWorkLoad(), node.getActWorkLoad(), node.getStartTime(), node.getEndTime(), node.getStatus(), node.getUserId(), node.getProxy(), node.getWeight(), node.getId() };
		int[] argTypes = new int[] { Types.INTEGER, Types.INTEGER , Types.INTEGER, Types.INTEGER, Types.TIMESTAMP, Types.TIMESTAMP, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER };
		jdbcTemplate.update(UPDATE_SQL, params, argTypes); 
	}
	
}
